package com.xoa.dev.pz.service.impl;

import com.xoa.dao.common.SysCodeMapper;
import com.xoa.dao.party.GridInfoMapper;
import com.xoa.dev.pz.dao.MenuJdtMapper;
import com.xoa.dev.pz.dao.PzDrugMapper;
import com.xoa.dev.pz.dao.PzPersonMapper;
import com.xoa.model.common.SysCode;
import com.xoa.model.enclosure.Attachment;
import com.xoa.model.party.GridInfo;
import com.xoa.dev.pz.model.PzDrug;
import com.xoa.dev.pz.model.PzDrugWithBLOBs;
import com.xoa.dev.pz.model.PzPersonWithBLOBs;
import com.xoa.dev.pz.model.PzPersonService;
import com.xoa.model.users.Users;
import com.xoa.service.sys.SysLogService;
import com.xoa.service.users.UsersService;
import com.xoa.util.DateFormat;
import com.xoa.util.ExcelUtil;
import com.xoa.util.GetAttachmentListUtil;
import com.xoa.util.ToJson;
import com.xoa.util.checknull.CheckUndefind;
import com.xoa.util.common.StringUtils;
import com.xoa.util.common.log.FileUtils;
import com.xoa.util.common.session.SessionUtils;
import com.xoa.util.netdisk.CheckAll;
import com.xoa.util.page.PageParams;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.*;
import java.lang.reflect.InvocationTargetException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by gsb on 2017/11/28.
 */
@Service
public class PzDrugService {

    @Resource
    private PzDrugMapper pzDrugMapper;

    @Resource
    private UsersService usersService;

    @Resource
    private PzPersonMapper pzPersonMapper;
    @Resource
    private SysLogService sysLogService;
    @Resource
    private SysCodeMapper sysCodeMapper;
    @Autowired
    GridInfoMapper gridInfoMapper;

    @Autowired
    private MenuJdtMapper menuJdtMapper;
    /**
     * 创建人：季佳伟
     * 创建时间：2017-11-28 下午15:26:17
     * 方法介绍：添加
     * 参数说明：
     */
    public ToJson<PzDrugWithBLOBs> insertSelective(HttpServletRequest request, PzDrugWithBLOBs record){
        ToJson<PzDrugWithBLOBs> json = new ToJson<>();
        Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
        try{
            record.setCreateUser(user.getUserId());
            Date date =new Date();
            SimpleDateFormat simpleDateFormat =new SimpleDateFormat("yyyy-MM-dd");
            String format = simpleDateFormat.format(date);
            record.setCreateTime(format);
            if(record.getMethadoneDetoxification()==null){
                record.setMethadoneDetoxification("0");
            }
            if(record.getCriminalHistory()==null){
                record.setCriminalHistory("0");
            }
            pzDrugMapper.insertSelective(record);
            sysLogService.setPzLog(request,user.getUserId(),date,"1","4","吸毒人员");
            json.setFlag(0);
            json.setMsg("ok");
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg("err");
            json.setFlag(1);
        }
        return json;
    }

    public ToJson<PzDrugWithBLOBs> updateByPrimaryKeySelective (HttpServletRequest request, PzDrugWithBLOBs record){
        ToJson<PzDrugWithBLOBs> json = new ToJson<>();
        try {
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            if(record.getMethadoneDetoxification()==null){
                record.setMethadoneDetoxification("0");
            }
            if(record.getCriminalHistory()==null){
                record.setCriminalHistory("0");
            }
            int count = pzDrugMapper.updateByPrimaryKeySelective(record);
            sysLogService.setPzLog(request,user.getUserId(),new Date(),"3","4","吸毒人员");
            if(count > 0){
                json.setFlag(0);
                json.setMsg("ok");
            }else {
                json.setMsg("err");
                json.setFlag(1);
            }

        }catch (Exception e){
            e.printStackTrace();
            json.setMsg("err");
            json.setFlag(1);
        }
        return json;
    }

    public ToJson<PzDrugWithBLOBs> deleteByPrimaryKey(HttpServletRequest request, Integer drugId){
        ToJson<PzDrugWithBLOBs> json = new ToJson<>();
        try {
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            pzDrugMapper.deleteByPrimaryKey(drugId);
            sysLogService.setPzLog(request,user.getUserId(),new Date(),"2","4","吸毒人员");
            json.setFlag(0);
            json.setMsg("ok");

        }catch (Exception e){
            e.printStackTrace();
            json.setMsg("err");
            json.setFlag(1);
        }
        return json;
    }
    public ToJson<PzDrug> queryPzDrug(Integer output, PzDrug pzDrug, Integer page, Integer pageSize, boolean useFlag,
                                      HttpServletRequest request, HttpServletResponse response, String pCradId, String pName){
        ToJson<PzDrug> json = new ToJson<PzDrug>();
        if("".equals(useFlag)){useFlag = true;}
        PageParams pageParams=new PageParams();
        pageParams.setPage(page);
        pageParams.setPageSize(pageSize);
        pageParams.setUseFlag(useFlag);
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("page", pageParams);
        map.put("pCradId", pCradId);
        map.put("pName", pName);

        try{
            Map<String, Object> param = new HashMap<String, Object>();
            Users user= SessionUtils.getSessionInfo(request.getSession(), Users.class, new Users());
            param.put("userId", user.getUserId());
            param.put("userPriv", user.getUserPriv());
            param.put("deptId", user.getDeptId());
            String menuModel=menuJdtMapper.selMenuByModel("4");
            if(!CheckAll.checkAll(menuModel,param)){
                pzDrug.setCreateUser(user.getUserId());
            }
            map.put("pzDrug",pzDrug);
            List<PzDrug> pzDruglist=pzDrugMapper.queryPzDrug(map);
            for(PzDrug pzDrug2:pzDruglist){
                pzDrug2.setGridUserNames(usersService.getUserNameById(pzDrug2.getGridUserIds()));
                CheckUndefind.checknull(pzDrug2);
                CheckUndefind.checknull(pzDrug2);
                for(PzPersonService pzPersonService:pzDrug2.getPzPersonServices()){
                    CheckUndefind.checknull(pzPersonService);
                }

                SysCode sysCode = sysCodeMapper.getSingleCode("RENT_CONCERN", pzDrug2.getAttention());
                if(sysCode!=null){
                    pzDrug2.setAttentionStr(sysCode.getCodeName());
                }
                SysCode sysCode1 = sysCodeMapper.getSingleCode("DRUG_STATE", pzDrug2.getDrugState());
                if(sysCode1!=null){
                    pzDrug2.setDrugStateStr(sysCode1.getCodeName());
                }
                SysCode sysCode2 = sysCodeMapper.getSingleCode("CONTROL_SITUATION", pzDrug2.getControlSituation());
                if(sysCode2!=null){
                    pzDrug2.setControlSituationStr(sysCode2.getCodeName());
                }
                SysCode sysCode3 = sysCodeMapper.getSingleCode("DRUG_CAUSE", pzDrug2.getDrugCause());
                if(sysCode3!=null){
                    pzDrug2.setDrugCauseStr(sysCode3.getCodeName());
                }
                SysCode sysCode4 = sysCodeMapper.getSingleCode("DRUG_SOURCE", pzDrug2.getDrugSource());
                if(sysCode4!=null){
                    pzDrug2.setDrugSourceStr(sysCode4.getCodeName());
                }
                SysCode sysCode5 = sysCodeMapper.getSingleCode("DRUG_SITUATION", pzDrug2.getDrugSituation());
                if(sysCode5!=null){
                    pzDrug2.setDrugSituationStr(sysCode5.getCodeName());
                }
                SysCode sysCode6 = sysCodeMapper.getSingleCode("POLITICAL", pzDrug2.getpPolitical());
                if(sysCode6!=null){
                    pzDrug2.setpPoliticalStr(sysCode6.getCodeName());
                }
                SysCode sysCode7 = sysCodeMapper.getSingleCode("DEGREE", pzDrug2.getpEducation());
                if(sysCode7!=null){
                    pzDrug2.setpEducationStr(sysCode7.getCodeName());
                }

                if ("0".equals(pzDrug2.getpSex())) {
                    pzDrug2.setpSexStr("男");
                } else {
                    pzDrug2.setpSexStr("女");
                }

                if ("01".equals(pzDrug2.getpMaritalStatus())) {
                    pzDrug2.setpMaritalStatusStr("未婚");
                } else {
                    pzDrug2.setpMaritalStatusStr("已婚");
                }
                if ("0".equals(pzDrug2.getpVehicles())) {
                    pzDrug2.setpVehiclesStr("无");
                } else {
                    pzDrug2.setpVehiclesStr("有");
                }
                if ("0".equals(pzDrug2.getMethadoneDetoxification())) {
                    pzDrug2.setMethadoneDetoxificationStr("无");
                } else if("1".equals(pzDrug2.getMethadoneDetoxification())){
                    pzDrug2.setMethadoneDetoxificationStr("有");
                } else {
                    pzDrug2.setMethadoneDetoxificationStr("");
                }
                if ("0".equals(pzDrug2.getCriminalHistory())) {
                    pzDrug2.setCriminalHistoryStr("无");
                } else if("1".equals(pzDrug2.getCriminalHistory())){
                    pzDrug2.setCriminalHistoryStr("有");
                }else {
                    pzDrug2.setCriminalHistoryStr("");
                }
            }

            if(output==1) {//output==1,则进行导出操作
                //this.outPut(request,response,pzDruglist);
                HSSFWorkbook workbook1 = ExcelUtil.makeExcelHead("吸毒人员信息导出", 30);
                String[] secondTitles = {"所属网格","姓名","性别","身份证", "曾用名/别名", "出生日期","民族","联系方式","政治面貌","文化程度","婚姻状况","宗教信仰","职业","工作单位","户籍地址","籍贯","户籍地详址","现居地址","名下机动车辆","备注", "关注程度","吸毒状态","初次发现时间","查货日期","管控情况", "毒品来源","戒毒情况","滥用毒品种类","犯罪情况","是否服美沙酮戒毒", "有无违法犯罪史"};
                HSSFWorkbook workbook2 = ExcelUtil.makeSecondHead(workbook1, secondTitles);
                String[] beanProperty = {"gridName","pName","pSexStr","pCradId", "pAlias","pDate","pNation","pPhone","pPoliticalStr","pEducationStr","pMaritalStatusStr","pReligion","pOccupation","pWorkUnit","pDomicile","pOrigin","pDetailedDomicile","pAddress","pVehiclesStr","pRemarks", "attentionStr","drugStateStr","firstFindTime","seizedDate", "controlSituationStr","drugSourceStr","drugSituationStr","drugAbuse","criminalSituation", "methadoneDetoxificationStr","criminalHistoryStr"};
                HSSFWorkbook workbook = null;
                workbook = ExcelUtil.exportExcelData(workbook2, pzDruglist, beanProperty);
                OutputStream out = response.getOutputStream();
                String filename = "吸毒人员信息记录表.xls";
                filename = FileUtils.encodeDownloadFilename(filename, request.getHeader("user-agent"));
                response.setContentType("application/vnd.ms-excel;charset=UTF-8");
                response.setHeader("content-disposition", "attachment;filename=" + filename);
                workbook.write(out);
                out.close();
            }
            json.setObj(pzDruglist);
            json.setTotleNum(pageParams.getTotal());
            json.setMsg("ok");
            json.setFlag(0);



        }catch (Exception e) {
            e.printStackTrace();
            json.setMsg("err");
            json.setFlag(1);
        }
        return json;

    }

    public ToJson<PzDrug> selectById(Integer drugId, HttpServletRequest request){
        ToJson<PzDrug> json = new ToJson<>();
        try {
            PzDrugWithBLOBs pzDrug = pzDrugMapper.selectById(drugId);
            //附件处理
            List<Attachment> attachmentList=new ArrayList<Attachment>();
            if(pzDrug.getAttachmentName()!=null&&!"".equals(pzDrug.getAttachmentName())){
                attachmentList= GetAttachmentListUtil.returnAttachment(pzDrug.getAttachmentName(),pzDrug.getAttachmentId(),"xoa" + (String) request.getSession().getAttribute("loginDateSouse"), GetAttachmentListUtil.MODULE_PZ_DRUG);
            }
            pzDrug.setAttachmentList(attachmentList);
            json.setObject(pzDrug);
            json.setFlag(0);
            json.setMsg("ok");
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg("err");
            json.setFlag(1);
        }
        return json;

    }

    /**
     * 创建作者:   刘新婷
     * 创建日期:   2017年12月03日
     * 方法介绍:   吸毒人员查看详情
     * 参数说明:   吸毒人员表drug_Id
     * 返回值说明:
     */
    public ToJson<PzDrugWithBLOBs> selectInfo(Integer drugId){
        ToJson<PzDrugWithBLOBs> json = new ToJson<>();
        try {
            PzDrugWithBLOBs pzDrug = pzDrugMapper.selectInfo(drugId);
            SysCode sysCode = sysCodeMapper.getSingleCode("RENT_CONCERN", pzDrug.getAttention());
            if(sysCode!=null){
                pzDrug.setAttentionStr(sysCode.getCodeName());
            }
            SysCode sysCode1 = sysCodeMapper.getSingleCode("DRUG_STATE", pzDrug.getDrugState());
            if(sysCode1!=null){
                pzDrug.setDrugStateStr(sysCode1.getCodeName());
            }
            SysCode sysCode2 = sysCodeMapper.getSingleCode("CONTROL_SITUATION", pzDrug.getControlSituation());
            if(sysCode2!=null){
                pzDrug.setControlSituationStr(sysCode2.getCodeName());
            }
            SysCode sysCode3 = sysCodeMapper.getSingleCode("DRUG_CAUSE", pzDrug.getDrugCause());
            if(sysCode3!=null){
                pzDrug.setDrugCauseStr(sysCode3.getCodeName());
            }
            SysCode sysCode4 = sysCodeMapper.getSingleCode("DRUG_SOURCE", pzDrug.getDrugSource());
            if(sysCode4!=null){
                pzDrug.setDrugSourceStr(sysCode4.getCodeName());
            }
            SysCode sysCode5 = sysCodeMapper.getSingleCode("DRUG_SITUATION", pzDrug.getDrugSituation());
            if(sysCode5!=null){
                pzDrug.setDrugSituationStr(sysCode5.getCodeName());
            }
            if(pzDrug.getPzPerson()!=null){
                SysCode sysCode6 = sysCodeMapper.getSingleCode("POLITICAL", pzDrug.getPzPerson().getpPolitical());
                if(sysCode6!=null){
                    pzDrug.getPzPerson().setpPoliticalStr(sysCode6.getCodeName());
                }
                SysCode sysCode7 = sysCodeMapper.getSingleCode("DEGREE", pzDrug.getPzPerson().getpEducation());
                if(sysCode7!=null){
                    pzDrug.getPzPerson().setpEducationStr(sysCode7.getCodeName());
                }
            }
            json.setObject(pzDrug);
            json.setFlag(0);
            json.setMsg("ok");
        }catch (Exception e){
            e.printStackTrace();
            json.setMsg("err");
            json.setFlag(1);
        }
        return json;

    }
    @Transactional
    public ToJson<PzDrugWithBLOBs> input(HttpServletRequest request, HttpServletResponse response, MultipartFile file, HttpSession session){
        ToJson<PzDrugWithBLOBs> json = new ToJson<PzDrugWithBLOBs>(1, "error");
        Users users= SessionUtils.getSessionInfo(request.getSession(),Users.class,new Users());
        // 成功次数
        Integer successCount = 0;
        try {
            //判读当前系统
            //读取配置文件
            ResourceBundle rb = ResourceBundle.getBundle("upload");
            String osName = System.getProperty("os.name");
            StringBuffer path = new StringBuffer();
            if (osName.toLowerCase().startsWith("win")) {
                path = path.append(rb.getString("upload.win"));
            } else {
                path = path.append(rb.getString("upload.linux"));
            }
            // 判断是否为空文件
            if (file.isEmpty()) {
                json.setMsg("请上传文件！");
                json.setFlag(1);
                return json;
            } else {
                String fileName = file.getOriginalFilename();
                if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                    String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                    int pos = fileName.indexOf(".");
                    String extName = fileName.substring(pos);
                    String newFileName = uuid + extName;
                    File dest = new File(path.toString(), newFileName);
//                    file.transferTo(dest);
                    // 将文件上传成功后进行读取文件
                    // 进行读取的路径
                 //   String readPath = path.append(newFileName).toString();
                    String readPath = path.append(System.getProperty("file.separator")).append(newFileName).toString();
//                    InputStream in = new FileInputStream(readPath);
                    InputStream in =  file.getInputStream();
                    HSSFWorkbook excelObj = new HSSFWorkbook(in);
                    HSSFSheet sheetObj = excelObj.getSheetAt(0);
                    Row row = sheetObj.getRow(0);
                    int colNum = row.getPhysicalNumberOfCells();
                    int lastRowNum = sheetObj.getLastRowNum();
                    List<PzDrugWithBLOBs> saveList = new ArrayList<PzDrugWithBLOBs>();
                    List<PzPersonWithBLOBs> saveList1 = new ArrayList<PzPersonWithBLOBs>();
                    PzDrugWithBLOBs pzDrugWithBLOBs = null;
                    PzPersonWithBLOBs pzPersonWithBLOBs = null;
                    SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
                    int count = 0;
                    for (int i = 1; i <= lastRowNum; i++) {
                        pzDrugWithBLOBs = new PzDrugWithBLOBs();
                        pzPersonWithBLOBs = new PzPersonWithBLOBs();
                        row = sheetObj.getRow(i);
                        String value = "";
                        if (row != null) {
                            for (int j = 0; j < colNum; j++) {
                                Cell cell = row.getCell(j);
                                if (cell != null) {
                                    switch (j) {
                                        case 0:
                                            //所属网格
                                            String as = cell.getStringCellValue();
                                            GridInfo info = gridInfoMapper.selectByUGridName(as);
                                            pzPersonWithBLOBs.setpGrid(info.getGridId());
                                            pzDrugWithBLOBs.setGridId(info.getGridId());
                                            break;
                                        case 1:
                                            // 姓名
                                            pzPersonWithBLOBs.setpName(cell.getStringCellValue());
                                            break;
                                        case 2:
                                            // 身份证
                                            pzPersonWithBLOBs.setpCradId(cell.getStringCellValue());
                                            break;
                                        case 3:
                                            // 别名
                                            pzPersonWithBLOBs.setpAlias(cell.getStringCellValue());
                                            break;
                                        case 4:
                                            // 性别
                                            if (cell.getStringCellValue() .equals("女")) {
                                                pzPersonWithBLOBs.setpSex("1");
                                            } else if (cell.getStringCellValue().equals("男")) {
                                                pzPersonWithBLOBs.setpSex("0");
                                            }
                                            break;
                                        case 5:
                                            // 出生日期
//                                            Date date = cell.getDateCellValue();
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                short format = cell.getCellStyle().getDataFormat();
                                                SimpleDateFormat sdf = null;
                                                if(format == 14 || format == 31 || format == 57 || format == 58){
                                                    //日期
                                                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                                                }else if (format == 20 || format == 32) {
                                                    //时间
                                                    sdf = new SimpleDateFormat("HH:mm");
                                                }
                                                double v = cell.getNumericCellValue();
                                                Date dates = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
//                                                date = sdf.format(v);
                                                pzDrugWithBLOBs.setpDate(dates);
                                                break;
                                            }
                                            if (cell.getStringCellValue()!=null){
                                                pzPersonWithBLOBs.setpDate(sd.parse(cell.getStringCellValue()));
                                            }else{

                                            }

                                            break;
                                        case 6:
                                            // 民族
                                            pzPersonWithBLOBs.setpNation(cell.getStringCellValue());
                                            break;
                                        case 7:
                                            // 联系方式
                                            if (cell.getCellType() ==CellType.NUMERIC) {
                                                BigDecimal bigP = new BigDecimal(cell.getNumericCellValue());
                                                value = bigP.toString();
                                                //解决1234.0  去掉后面的.0
                                                if (null != value && !"".equals(value.trim())) {
                                                    String[] item = value.split("[.]");
                                                    if (1 < item.length && "0".equals(item[1])) {
                                                        value = item[0];
                                                    }
                                                }
                                                pzPersonWithBLOBs.setpPhone(value);
                                            } else {
                                                pzPersonWithBLOBs.setpPhone(cell.getStringCellValue());
                                            }
                                            break;
                                        case 8:
                                            // 政治面貌
                                            pzPersonWithBLOBs.setpPolitical((sysCodeMapper.getByName("POLITICAL",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 9:
                                            // 文化程度
                                            pzPersonWithBLOBs.setpEducation((sysCodeMapper.getByName("DEGREE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 10:
                                            // 婚姻状况
                                            if (cell.getStringCellValue().equals("未婚")) {
                                                pzPersonWithBLOBs.setpMaritalStatus("01");
                                            } else if (cell.getStringCellValue().equals("已婚")) {
                                                pzPersonWithBLOBs.setpMaritalStatus("02");
                                            }
                                            break;
                                        case 11:
                                            //宗教信仰
                                            pzPersonWithBLOBs.setpReligion(cell.getStringCellValue());
                                            break;
                                        case 12:
                                            //职业
                                            pzPersonWithBLOBs.setpOccupation(cell.getStringCellValue());
                                            break;
                                        case 13:
                                            //工作单位
                                            pzPersonWithBLOBs.setpWorkUnit(cell.getStringCellValue());
                                            break;
                                        case 14:
                                            //户籍地址
                                            pzPersonWithBLOBs.setpDomicile(cell.getStringCellValue());
                                            break;
                                        case 15:
                                            //籍贯
                                            pzPersonWithBLOBs.setpOrigin(cell.getStringCellValue());
                                            break;
                                        case 16:
                                            //户籍地详址
                                            pzPersonWithBLOBs.setpDetailedDomicile(cell.getStringCellValue());
                                            break;
                                        case 17:
                                            //名下机动车辆
                                            if (cell.getStringCellValue().equals("有")) {
                                                pzPersonWithBLOBs.setpVehicles("0");
                                            } else if (cell.getStringCellValue().equals("无")) {
                                                pzPersonWithBLOBs.setpVehicles("1");
                                            }
                                            break;
                                        case 18:
                                            //备注
                                            pzPersonWithBLOBs.setpRemarks(cell.getStringCellValue());
                                            break;
                                        case 19:
                                            //有无固定住所
                                            if (cell.getStringCellValue().equals("无")) {
                                                pzPersonWithBLOBs.setpIsHouse("0");
                                            } else if (cell.getStringCellValue().equals("有")) {
                                                pzPersonWithBLOBs.setpIsHouse("1");
                                            }
                                            break;
                                        case 20:
                                            //现居地址
                                            pzPersonWithBLOBs.setpAddress(cell.getStringCellValue());
                                            break;
                                        case 21:
                                            //实口类型
                                            if (cell.getStringCellValue().equals("流动人口")) {
                                                pzPersonWithBLOBs.setFlag1("2");
                                            } else if (cell.getStringCellValue().equals("户籍人口")) {
                                                pzPersonWithBLOBs.setFlag1("1");
                                            }
                                            break;
                                        case 22:
                                            //车牌号
                                            cell.setCellType(CellType.STRING);
                                            pzPersonWithBLOBs.setStr1(cell.getStringCellValue());
                                            break;
                                        case 23:
                                            //关注程度
                                            pzDrugWithBLOBs.setAttention((sysCodeMapper.getByName("RENT_CONCERN",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 24:
                                            // 吸毒状态
                                            pzDrugWithBLOBs.setDrugState((sysCodeMapper.getByName("DRUG_STATE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 25:
                                            // 初次发现时间
                                            String result = null;
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                short format = cell.getCellStyle().getDataFormat();
                                                SimpleDateFormat sdf = null;
                                                if(format == 14 || format == 31 || format == 57 || format == 58){
                                                    //日期
                                                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                                                }else if (format == 20 || format == 32) {
                                                    //时间
                                                    sdf = new SimpleDateFormat("HH:mm");
                                                }
                                                double v = cell.getNumericCellValue();
                                                Date dates = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
                                                result = sdf.format(dates);
                                                pzDrugWithBLOBs.setFirstFindTime(result);
                                                break;
                                            }
                                            pzDrugWithBLOBs.setFirstFindTime(cell.getStringCellValue());
                                            break;
                                        case 26:
                                            // 查获日期
                                            String result1 = null;
                                            if(cell.getCellType() == CellType.NUMERIC){
                                                short format = cell.getCellStyle().getDataFormat();
                                                SimpleDateFormat sdf = null;
                                                if(format == 14 || format == 31 || format == 57 || format == 58){
                                                    //日期
                                                    sdf = new SimpleDateFormat("yyyy-MM-dd");
                                                }else if (format == 20 || format == 32) {
                                                    //时间
                                                    sdf = new SimpleDateFormat("HH:mm");
                                                }
                                                double v = cell.getNumericCellValue();
                                                Date dates = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(v);
                                                result1 = sdf.format(dates);
                                                pzDrugWithBLOBs.setSeizedDate(result1);
                                                break;
                                            }
                                            pzDrugWithBLOBs.setSeizedDate(cell.getStringCellValue());
                                            break;
                                        case 27:
                                            // 管控情况
                                            pzDrugWithBLOBs.setControlSituation((sysCodeMapper.getByName("CONTROL_SITUATION",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 28:
                                            // 吸毒原因
                                            pzDrugWithBLOBs.setDrugCause((sysCodeMapper.getByName("DRUG_CAUSE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 29:
                                            // 毒品来源
                                            pzDrugWithBLOBs.setDrugSource((sysCodeMapper.getByName("DRUG_SOURCE",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 30:
                                            // 戒毒情况
                                            pzDrugWithBLOBs.setDrugSituation((sysCodeMapper.getByName("DRUG_SITUATION",cell.getStringCellValue().trim())).getCodeNo());
                                            break;
                                        case 31:
                                            //滥用毒品种类
                                            cell.setCellType(CellType.STRING);
                                            pzDrugWithBLOBs.setDrugAbuse(cell.getStringCellValue());
                                            break;
                                        case 32:
                                            //犯罪情况
                                            cell.setCellType(CellType.STRING);
                                            pzDrugWithBLOBs.setCriminalSituation(cell.getStringCellValue());
                                            break;
                                        case 33:
                                            //是否服美沙酮戒毒
                                            if (cell.getStringCellValue().equals("有")) {
                                                pzDrugWithBLOBs.setMethadoneDetoxification("1");
                                            } else if (cell.getStringCellValue().equals("无")) {
                                                pzDrugWithBLOBs.setMethadoneDetoxification("0");
                                            }
                                            break;
                                        case 34:
                                            //有无违法犯罪史
                                            if (cell.getStringCellValue().equals("有")) {
                                                pzDrugWithBLOBs.setCriminalHistory("1");
                                            } else if (cell.getStringCellValue().equals("无")) {
                                                pzDrugWithBLOBs.setCriminalHistory("0");
                                            }
                                            break;
                                        default:
                                            json.setFlag(0);
                                            json.setMsg("err");
                                            break;
                                    }
                                }
                            }
                            List<String> nullArray = new ArrayList();
                            if (pzPersonWithBLOBs.getpGrid() == null) {
                                nullArray.add("所属网格");
                            }
                            if (pzPersonWithBLOBs.getpName() == null) {
                                nullArray.add("姓名");
                            }
                            if (pzPersonWithBLOBs.getpSex() == null) {
                                nullArray.add("性别");
                            }
                            if (pzPersonWithBLOBs.getpCradId() == null) {
                                nullArray.add("身份证号");
                            }
                            if (pzPersonWithBLOBs.getFlag1() == null) {
                                nullArray.add("实口类型");
                            }
                            if (pzPersonWithBLOBs.getpOrigin() == null) {
                                nullArray.add("籍贯");
                            }
                            if (pzPersonWithBLOBs.getpIsHouse() == null) {
                                nullArray.add("有无固定住所");
                            }
                            if (pzPersonWithBLOBs.getpAddress() == null) {
                                nullArray.add("现居地址");
                            }
                            if (pzPersonWithBLOBs.getpMaritalStatus() == null) {
                                nullArray.add("婚姻状况");
                            }
                            if (pzPersonWithBLOBs.getpDomicile() == null) {
                                nullArray.add("户籍地址");
                            }
                            if (pzPersonWithBLOBs.getpDetailedDomicile() == null) {
                                nullArray.add("户籍地详情");
                            }
                            if (pzPersonWithBLOBs.getpVehicles() == null) {
                                nullArray.add("名下机动车辆");
                            }
                            if (pzDrugWithBLOBs.getControlSituation() == null) {
                                nullArray.add("管控情况");
                            }
                            if (pzDrugWithBLOBs.getDrugCause() == null) {
                                nullArray.add("吸毒原因");
                            }
                            if (pzDrugWithBLOBs.getDrugSource() == null) {
                                nullArray.add("毒品来源");
                            }
                            if (pzDrugWithBLOBs.getDrugSituation() == null) {
                                nullArray.add("戒毒情况");
                            }
                            if (nullArray.size() > 0) {
                                json.setObject(nullArray);
                                json.setMsg("必填字段不能为空，请重新导入");
                            } else {
                                //判断用户是否为空，不为空判断用户是否存在
                                if (!StringUtils.checkNull(pzPersonWithBLOBs.getpName()) && !StringUtils.checkNull(pzPersonWithBLOBs.getpCradId())) {
                                    Map<String, Object> map = new HashMap<String, Object>();
                                    map.put("pCradId", pzPersonWithBLOBs.getpCradId());
                                    map.put("pName", pzPersonWithBLOBs.getpName());
                                    PzPersonWithBLOBs pzPersons = pzPersonMapper.selectPersonCountByNameCrad(map);
                                    if (pzPersons.getCount() > 0) {
                                        pzPersonWithBLOBs.setpId(pzPersons.getpId());
                                        pzPersonWithBLOBs.setFlag2("0");
                                        if(!pzPersons.getSpecialStr().contains("5,")){
                                            String str = "5," + pzPersons.getSpecialStr();
                                            pzPersonWithBLOBs.setSpecialStr(str);
                                        }
                                        pzDrugWithBLOBs.setUserId(String.valueOf(pzPersons.getpId()));
                                        pzPersonMapper.updateByPrimaryKeySelective(pzPersonWithBLOBs);
                                    } else {
                                        //创建用户和创建时间
                                        pzPersonWithBLOBs.setCreateUser(users.getUserId());
                                        pzPersonWithBLOBs.setCreateTime(DateFormat.getCurrentTime());
                                        pzPersonWithBLOBs.setFlag2("0");
                                        pzPersonWithBLOBs.setSpecialStr("5,");
                                        pzPersonMapper.insertSelective(pzPersonWithBLOBs);
                                        pzDrugWithBLOBs.setUserId(String.valueOf(pzPersonWithBLOBs.getpId()));
                                    }
                                }
                                //将数据放入数据库
                                //用户表与表关联用户userid
                                pzDrugWithBLOBs.setUserId(String.valueOf(pzPersonWithBLOBs.getpId()));
                                //判断关联用户userid是否为空,为空新增,不为空修改
                                if(!StringUtils.checkNull(String.valueOf(pzDrugWithBLOBs.getUserId()))){
                                    //如果不为空，查询是否有信息，有则更新，否则新增
                                    Map<String, Object> map = new HashMap<String, Object>();
                                    map.put("userId", pzDrugWithBLOBs.getUserId());
                                    PzDrug pzDrug=pzDrugMapper.selectInfoByUserId(map);
                                    if(pzDrug.getCou()>0){
                                        pzDrugWithBLOBs.setDrugId(pzDrug.getDrugId());
                                        count += pzDrugMapper.updateByPrimaryKeySelective(pzDrugWithBLOBs);
                                    }else {
                                        //创建用户和创建时间
                                        pzDrugWithBLOBs.setCreateUser(users.getUserId());
                                        pzDrugWithBLOBs.setCreateTime(DateFormat.getCurrentTime());
                                        count += pzDrugMapper.insertSelective(pzDrugWithBLOBs);
                                    }
                                }
                        }
                    }
                    }
                    if(count>0) {
                        successCount = successCount + count;
                    }
                    dest.delete();
                    if(successCount>0){
                        json.setTotleNum(successCount);
                        json.setMsg("ok");
                        json.setFlag(0);
                        return  json;
                    }

                }
            }
        } catch (Exception e) {
            //读取配置文件,获取文件要存储的路径
            StringBuffer path = this.getPath();
            String fileName = file.getOriginalFilename();
            if (fileName.endsWith(".xls") || fileName.endsWith(".xlsx")) {
                String uuid = UUID.randomUUID().toString().replaceAll("-", "");
                String extName = fileName
                        .substring(fileName.lastIndexOf(".") + 1);// 文件后缀
                String newFileName = uuid + extName;//组合成新的文件名
                File dest = new File(path.toString(), newFileName);
                dest.delete();
            }
            e.getMessage();
            e.printStackTrace();
        }

        return json;
    }

    //判读当前系统,读取配置文件,返回路径
    public StringBuffer getPath(){
        ResourceBundle rb = ResourceBundle.getBundle("upload");
        String osName = System.getProperty("os.name");
        StringBuffer path = new StringBuffer();
        if (osName.toLowerCase().startsWith("win")) {
            path = path.append(rb.getString("upload.win"));
        } else {
            path = path.append(rb.getString("upload.linux"));
        }
        return path;
    }

    public void outPut(HttpServletRequest request, HttpServletResponse response, List<PzDrug> pzDruglist) throws IllegalAccessException, NoSuchMethodException, InvocationTargetException, IOException {
        for (PzDrug pzDrug3:pzDruglist){
            pzDrug3.setpName(pzDrug3.getPzPerson().getpName());
            String temp;
            pzDrug3.setAttention(pzDrug3.getAttention().equals("1")?"一般": pzDrug3.getAttention().equals("2")?"中等": "严重");
            pzDrug3.setDrugState(pzDrug3.getDrugState().equals("1")?"在吸":"停吸");
            pzDrug3.setControlSituation(pzDrug3.getControlSituation().equals("1")?"在控":pzDrug3.getControlSituation().equals("2")?"失控":
                    pzDrug3.getControlSituation().equals("2")?"死亡":pzDrug3.getControlSituation().equals("4")?"出国（境）":"社会面上戒断三年未复吸");
            pzDrug3.setDrugCause(pzDrug3.getDrugCause().equals("1")?"亲友影响":pzDrug3.getDrugCause().equals("2")?"被诱骗":
                    pzDrug3.getDrugCause().equals("3")?"被强迫":pzDrug3.getDrugCause().equals("4")?"好奇":pzDrug3.getDrugCause().equals("5")?"治病引起":
                            pzDrug3.getDrugCause().equals("6")?"人生受挫":pzDrug3.getDrugCause().equals("7")?"寻找刺激":pzDrug3.getDrugCause().equals("8")?"遗传":"其他");
            temp=pzDrug3.getDrugSource();
            pzDrug3.setDrugSource(temp.equals("1")?"黑市购买":temp.equals("2")?"他人提供":temp.equals("3")?"偷盗":temp.equals("4")?"医生处方":
                    temp.equals("5")?"药店":temp.equals("6")?"自制":"其他");
            temp=pzDrug3.getDrugSituation();
            pzDrug3.setDrugSituation(temp.equals("1")?"强制戒毒":temp.equals("2")?"劳教戒毒":temp.equals("3")?"限期戒毒":
                    temp.equals("4")?"自愿戒毒":temp.equals("5")?"社区戒毒":"其他");
            temp=pzDrug3.getMethadoneDetoxification();
            pzDrug3.setMethadoneDetoxification(temp.equals("0")?"无":"有");
            temp=pzDrug3.getCriminalHistory();
            pzDrug3.setCriminalHistory(temp.equals("0")?"无":"有");

            temp=pzDrug3.getPzPerson().getpSex();
            pzDrug3.getPzPerson().setpSex(temp.equals("0")?"男":"女");
            temp=pzDrug3.getPzPerson().getpMaritalStatus();
            pzDrug3.getPzPerson().setpMaritalStatus(temp.equals("0")?"未婚":"已婚");
            temp=pzDrug3.getPzPerson().getpDie();
            pzDrug3.getPzPerson().setpDie(temp.equals("0")?"否":"是");
            temp=pzDrug3.getPzPerson().getpVehicles();
            pzDrug3.getPzPerson().setpVehicles(temp.equals("0")?"无":"有");
            temp=pzDrug3.getPzPerson().getpIsHouse();
            pzDrug3.getPzPerson().setpIsHouse(temp.equals("0")?"无":"有");
            temp=pzDrug3.getPzPerson().getFlag1();
            pzDrug3.getPzPerson().setFlag1(temp.equals("1")?"户籍人口":"流动人口");

        }
        HSSFWorkbook tableWork = ExcelUtil.makeExcelHead("吸毒人员表", 16);
        String[] secondTitles = {"吸毒人员编号","姓名", "关注程度","吸毒状态","初次发现时间","查货日期","管控情况",
                "吸毒原因","毒品来源","戒毒情况","滥用毒品种类","犯罪情况","是否服美沙酮戒毒",
                "有无违法犯罪史","关联用户","网格id","备注"};
        HSSFWorkbook excelWork = ExcelUtil.makeSecondHead(tableWork, secondTitles);
        String[] beanProperty = {"drugId","pName","attention","drugState","firstFindTime","seizedDate",
                "controlSituation","drugCause","drugSource","drugSituation","drugAbuse","criminalSituation",
                "methadoneDetoxification","criminalHistory","userId","gridId","remark1"};
        HSSFWorkbook workbook = ExcelUtil.exportExcelData(excelWork, pzDruglist, beanProperty);
        response.setContentType("text/html;charset=UTF-8");
        OutputStream out = response.getOutputStream();

        String filename = "吸毒人员表.xls"; //考虑多语言
        filename = FileUtils.encodeDownloadFilename(filename,
                request.getHeader("user-agent"));
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("content-disposition",
                "attachment;filename=" + filename);
        workbook.write(out);
        out.flush();
        out.close();

    }
}
